<?php
class Model_campaign extends Model {

    function Model_campaign() {
        parent::Model();
    }


    /**
     * Functie care aduce toate contactele de pe campanie ( baza de date , externe)
     */
    function bringCampaignContact($s_limit, $e_limit, $sort, $dir, $data) {

        $filtru_ext = "";
        $filtru_base = "";

        $aFieldsSearch = json_decode($data["fields"]);
        $sQuerySearch = $data["query"];

        if (count($aFieldsSearch) > 0) {
            foreach ($aFieldsSearch as $key => $sField) {
                if ($key == 0) {
                    $filtru_ext .= " AND (";
                    $filtru_base .= "AND (";
                }
                $filtru_ext .= "  client_persons.{$sField} LIKE   '%" . $sQuerySearch . "%' ";
                $filtru_base .= " persons.{$sField} LIKE   '%" . $sQuerySearch . "%' ";
                if ($key < count($aFieldsSearch) - 1) {
                    $filtru_ext .= " OR";
                    $filtru_base .= " OR";
                }
                if ($key == count($aFieldsSearch) - 1) {
                    $filtru_ext .= " )";
                    $filtru_base .= " )";
                }
            }
        }

        if ($data["lname"] != "") {
            $lname = $this->db->escape_like_str($data["lname"]);

            $filtru_ext .= " AND client_persons.lname LIKE   '%" . $lname . "%' ";
            $filtru_base .= " AND persons.lname LIKE   '%" . $lname . "%' ";
        }

        if ($data["fname"] != "") {
            $fname = $this->db->escape_like_str($data["fname"]);
            $filtru_ext .= " AND client_persons.fname LIKE   '%" . $fname . "%' ";
            $filtru_base .= " AND persons.fname LIKE   '%" . $fname . "%' ";
        }

        if ($data["client_id"] != "") {
            $client_id = $this->db->escape($data["client_id"]);
            $filtru_ext .= " AND client_persons.client_id =  {$client_id} ";
            $filtru_base .= " AND client_persons.client_id =  {$client_id} ";
        }

        if ($data["extern"] != "") {
            $extern = $this->db->escape($data["extern"]);
            $filtru_ext .= " AND extern =  {$extern} ";
            $filtru_base .= " AND extern =  {$extern} ";
        }

        if ($data["client_id"] == "") {
            return array(
                "error" => true,
                "type" => "performance",
                "description" => "Selectati cel putin o campanie ",
                'totalCount' => 0,
                'data' => array()
            );
        }


        $select_dbl = "(
        (select client_persons_id, persons.lname, `persons`.`fname`,`persons`.`company`,  `client`.`name` as client_name, `client_persons`.`client_id`, `extern` , persons.email, persons.email2, persons.sex,client_persons.unsubscribed
             from client_persons
              left join client on client.client_id=client_persons.client_id
              left join persons on persons.person_id=client_persons.person_id 
             where extern=0  {$filtru_base} )
         UNION ALL
            ( select client_persons_id,  lname, `fname`,company,`client`.`name` as client_name,client.`client_id`, `extern`,client_persons.email,client_persons.email2, client_persons.sex,unsubscribed
             from client_persons
             left join client on client.client_id=client_persons.client_id
             where extern=1 {$filtru_ext} )) as P1 ";

        $query = $this->db->query("select SQL_CALC_FOUND_ROWS * from {$select_dbl}   where 1=1   ORDER BY {$sort}  {$dir} LIMIT {$s_limit},{$e_limit} ");

        $result = $query->result_array();
        $totalCount = $this->db->query('SELECT FOUND_ROWS() total_rows')->row()->total_rows;

        $data = array(
            "error" => false,
            "type" => "silent",
            'totalCount' => $totalCount,
            'data' => $result
        );
        return $data;
    }

    function bringCampaign($table, $s_limit, $e_limit, $sort, $dir, $data) {

        $this->db->select(" SQL_CALC_FOUND_ROWS client.client_id, name, nr", false);
        $this->db->join("  ( select count(person_id) as nr,client_id from client_persons where extern=0 group by client_id  ) as M ", "M.client_id={$table}.client_id", "left");

        if ((isset($data["query"])) && ($data["query"] != "")) {
            $this->db->like("name", $data['query']);
        }

        if ($e_limit != "") {
            $this->db->limit($e_limit, $s_limit);
        }
        if ($sort != "") {
            $this->db->order_by($sort, $dir);
        }
        $result = $this->db->get("client")->result();

        $totalCount = $this->db->query('SELECT FOUND_ROWS() total_rows')->row()->total_rows;

        $data = array(
            'totalCount' => $totalCount,
            'data' => $result
        );
        return $data;
    }

    function deleteCampaign($data) {
        $this->db->flush_cache();
        $this->db->where("client_id", $data["client_id"]);
        $this->db->delete("client");
        //se vor sterge automat si persoanele atasate campaniilor din cauza la FK        
    }


    function opContact($data) {


        if ($data["op"] == 1) {

            foreach (json_decode($data["contacts"]) as $key => $contact) {
                //print_r ($contact);
                $this->db->flush_cache();
                $this->db->where("client_id", $data["client_id"]);
                $this->db->where("person_id", $contact);
                $query = $this->db->get("client_persons");
                if ($query->num_rows() == 0) {
                    $this->db->flush_cache();
                    $data_2_insert = array("client_id" => $data["client_id"], "person_id" => $contact);
                    $result = $this->db->insert("client_persons", $data_2_insert);

                    //se face update pe contact.. se pune ultima campanie a contactului din tabela client_persons
                    $this->db->flush_cache();
                    $this->db->select("client.name");
                    $this->db->where("client_id", $data["client_id"]);
                    $sClientName = $this->db->get("client")->row()->name;

                    $aTemp = array(
                        "client_id" => $data["client_id"],
                        "client_name" => $sClientName
                    );
                    $this->db->flush_cache();
                    $this->db->where("person_id", $contact);
                    $this->db->update("persons", $aTemp);
                }
            }
        }
        if ($data["op"] == 0) {
            foreach (json_decode($data["contacts"]) as $key => $contact) {
                $this->db->flush_cache();
                $this->db->where("client_id", $data["client_id"]);
                $this->db->where("person_id", $contact);
                $result = $this->db->delete("client_persons");

                //modificat noul id si nume din campurile client_id, client_name
                //1. scot ultimul client atasat persoanei
                $this->db->flush_cache();
                $this->db->select("client.name as client_name,client.client_id");
                $this->db->where("client_persons.person_id", $contact);
                $this->db->order_by("client_persons.update_date", "desc");
                $this->db->join("client", "client.client_id=client_persons.client_id", "inner");
                $aClient = $this->db->get("client_persons")->row_array();

                //fac update pe contact
                $this->db->flush_cache();
                $this->db->where("person_id", $contact);
                $this->db->update("persons", array("client_name" => $aClient["client_name"], "client_id" => $aClient["client_id"]));
            }
        }
    }

    /**
     * Functie care dezaboneaza / aboneaza sau sterge de pe o campanie
     */
    function opCampaignContact($data) {

        $dataUpdate = array();
        if (($data["op"] == '0') || ($data["op"] == '1')) {
            $this->db->flush_cache();
            $this->db->where_in("client_persons_id", json_decode($data["contact"]));
            $dataUpdate = array("unsubscribed" => $data["op"]);
            $query = $this->db->update("client_persons", $dataUpdate);
        }

        if ($data["op"] == '10') {
            $this->db->flush_cache();
            $this->db->where_in("client_persons_id", json_decode($data["contact"]));
            $query = $this->db->delete("client_persons");
        }
        return $query;
    }


    function addContact($data) {

        $temp = array("lname" => strTitle($data["lname"]),
                      "fname" => strTitle($data["fname"]),
                      "email" => $data["email"],
                      "email2" => $data["email2"],
                      "company" => strUpper($data["company"]),
                      "sex" => $data["sex"]
        );

        $this->db->flush_cache();
        $this->db->where("client_id", $data["client"]);
        //$this->db->set("person_id","1");
        $result = $this->db->insert("client_persons", $data);
        return $result;
    }


    function getContact($data) {
        $this->db->flush_cache();
        $this->db->where("client_persons_id", $data['client_persons_id']);
        $query = $this->db->get("client_persons");
        $aContact = $query->row_array();

        if ($aContact["extern"] == 1) {
            return $aContact;
        }
        else {
            $this->db->flush_cache();
            $this->db->where("person_id", $aContact['person_id']);
            $query = $this->db->get("persons");
            $aContactBaza = $query->row_array();

            $aContactBaza["client_persons_id"] = $data["client_persons_id"];
            return $aContactBaza;
        }

    }

    function editContact($data) {

        $temp = array("lname" => strTitle($data["lname"]),
                      "fname" => strTitle($data["fname"]),
                      "email" => $data["email"],
                      "email2" => $data["email2"],
                      "sex" => $data["sex"]
        );

        if ($data["company"] != "") {
            $temp["company"] = strUpper($data["company"]);
        }

        $this->db->flush_cache();
        $this->db->where("client_persons_id", $data["client_persons_id"]);
        $result = $this->db->get("client_persons", $data)->row_array();

        if ($result["extern"] == 1) {
            $this->db->flush_cache();
            $this->db->where("client_persons_id", $data["client_persons_id"]);
            $this->db->update("client_persons", $temp);
            return true;
        }
        else {
            $this->db->flush_cache();
            $this->db->where("person_id", $result["person_id"]);
            $this->db->update("persons", $temp);
            return true;
        }

    }

	function getCampaignData($aData) {
		
		
		$aFieldsSearch = json_decode($aData["fields"]);
        $sQuerySearch = $aData["query"];

        if (count($aFieldsSearch) > 0) {
            foreach ($aFieldsSearch as $key => $sField) {
                if ($key == 0) {
                    $filtru .= "  (";                 
                }
                $filtru .= "  client_persons.{$sField} LIKE   '%" . $sQuerySearch . "%' OR persons.{$sField} LIKE   '%" . $sQuerySearch . "%' ";
                if ($key < count($aFieldsSearch) - 1) {
                    $filtru .= " OR";                   
                }
                if ($key == count($aFieldsSearch) - 1) {
                    $filtru .= " )";                
                }
            }
        }

        if ($aData["lname"] != "") {
            $lname = $this->db->escape_like_str($aData["lname"]);
            $filtru.= "  (client_persons.lname LIKE   '%" . $lname . "%' OR  persons.lname LIKE   '%" . $lname . "%' )";
        }

        if ($aData["fname"] != "") {
            $fname = $this->db->escape_like_str($aData["fname"]);
            $filtru.= " (client_persons.fname LIKE   '%" . $fname . "%'  AND persons.fname LIKE   '%" . $fname . "%') ";
        }
		
		
		if($aData["extern"] == 1) {
			$this->db->where("client_persons.person_id ", 1);
		}
		if($aData["extern"] == 0) {
			$this->db->where("client_persons.person_id >", 1);
		}
		
		$this -> db -> select(" persons.*,client.name as campanie, client_persons.unsubscribed as unsubscribed_campanie,
				`domain`.name as domain_name,   `SD`.name as subdomain_name,
				IF(extern =1 , client_persons.company , persons.company) as company,
	            IF(extern =1 , client_persons.lname , persons.lname) as lname,
	            IF(extern =1 , client_persons.fname , persons.fname) as fname,
	            IF(extern =1 , CP.name , P.name) as position_name,
	            IF(extern =1 , CSP.name , SP.name) as subposition_name,
	            IF(extern =0 , persons.phone , client_persons.phone) as phone,
	            IF(extern =0 , persons.mobil , client_persons.mobil) as mobil,
	            IF(extern =1 , client_persons.email , persons.email) as email,
	            IF(extern =1 , client_persons.email2 , persons.email2) as email2,
	            IF(extern =1 , CC.name , city.name) as city_id_val,
	            IF(extern =1 , client_persons.city_id , persons.city_id) as city_id,
	            IF(extern =1 , CCo.name , county.name) as county_id_val,
	            IF(extern =1 , client_persons.sex , persons.sex) as sex,
	            IF(extern =1 , client_persons.web , persons.web) as web		
		 ", false);
			$this->db->join("client_persons", "client_persons.person_id=persons.person_id", "left");
			$this -> db -> join("client", "client_persons.client_id=client.client_id", "left");
			
			$this -> db -> join("domain", "domain.domain_id=persons.domain_id", "left");
			$this -> db -> join("domain SD", "persons.domain_sub_id=SD.domain_id  ", "left");
			
			$this -> db -> join("division", "division.division_id=persons.division_id", "left");
			
			
			$this -> db -> join("`position` P", "persons.position_id=P.position_id  ", "left");			
			$this -> db -> join("`position` CP", "client_persons.position_id=CP.position_id  ", "left");
			
			$this -> db -> join("`position` SP", "persons.position_sub_id=SP.position_id  ", "left");			
			$this -> db -> join("`position` CSP", "client_persons.position_sub_id=CSP.position_id  ", "left");
			
			$this -> db -> join("city", "persons.city_id=city.city_id ", "left");
			$this -> db -> join("city CC", "client_persons.city_id=CC.city_id ", "left");
			
			$this -> db -> join("county", "persons.county_id=county.county_id", "left");
			$this -> db -> join("county CCo", "client_persons.county_id=CCo.county_id", "left");            
	
			
			$this->db->where("client_persons.client_id", $aData["client_id"]);
			if($filtru != "") {
				$this->db->where($filtru, null, false);
			}			
			$aData = $this->db->get("persons")->result_array();
			return $aData;		
	}

}//end model